﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Models;
using System.Data.SQLite;

namespace DAL
{
    public class VIPManager
    {
        /// <summary>
        /// 是否是vip顾客
        /// </summary>
        /// <param name="customer">顾客对象</param>
        /// <returns>是VIP返回tru，否则返回false</returns>
        public bool IsIncludeVIP(Customer customer)
        {
            string sql = string.Format("select * from tb_VIP where tb_VIP.身份证号='{0}'",customer.IDCardNum);
            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            if (rd.HasRows)
            {
                rd.Close();
                return true;
            }
            else
            {
                rd.Close();
                return false;
            }
        }

        /// <summary>
        /// 获取vip折率
        /// </summary>
        /// <returns></returns>
        public Object GetVIPDiscount()
        {
            string sql = "select VIP折率 from tb_Discount";
            return SQLHelper.GetSingleResult(sql);
        }

        /// <summary>
        /// 获取全部VIP信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllVIPInfoDataSet()
        {
            string sql = "select * from tb_VIP";
            return SQLHelper.GetDataSet(sql, "allVIPInfo");
        }


        /// <summary>
        /// 通过身份证号获取VIP信息
        /// </summary>
        /// <param name="IDCardNum"></param>
        /// <returns></returns>
        public DataSet GetVIPInfoDataSetByIDCardNum(string IDCardNum)
        {
            string sql = string.Format("select * from tb_VIP where 身份证号='{0}'",IDCardNum);
            return SQLHelper.GetDataSet(sql, "VIPInfoIDCardNum");
        }

        /// <summary>
        /// 通过姓名获取VIP信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataSet GetVIPInfoDataSetByName(string name)
        {
            string sql = string.Format("select * from tb_VIP where 姓名 like '%{0}%'", name);
            return SQLHelper.GetDataSet(sql, "VIPInfoIDCardNum");
        }


        /// <summary>
        /// 通过身份证号判断是否已存在
        /// </summary>
        /// <param name="idCardNum"></param>
        /// <returns></returns>
        public bool IsExistByIDCard(String idCardNum)
        {
            string sql = string.Format("select * from tb_VIP where 身份证号='{0}'",idCardNum);
            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            if (rd.HasRows)
            {
                rd.Close();
                return true;
            }
            else
            {
                rd.Close();
                return false;
            }
        }





        /// <summary>
        /// 添加VIP
        /// </summary>
        /// <param name="vIP"></param>
        /// <returns></returns>
        public int AddVIP(VIP vIP) 
        {
            String sql = string.Format("insert into tb_VIP (身份证号,姓名,性别,年龄) values('{0}','{1}','{2}',{3})", vIP.IDCardNum, vIP.Name, vIP.Sex, vIP.Age);
            return SQLHelper.Update(sql);
        }

        /// <summary>
        /// 修改、更新VIP信息
        /// </summary>
        /// <param name="vIP"></param>
        /// <returns></returns>
        public int UpdateVIP(VIP vIP)
        {
            String sql = string.Format("update tb_VIP set 身份证号='{0}',姓名='{1}',性别='{2}',年龄={3} where ID={4}", vIP.IDCardNum, vIP.Name, vIP.Sex, vIP.Age,vIP.ID);
            return SQLHelper.Update(sql);
        }

        /// <summary>
        /// 删除VIP信息
        /// </summary>
        /// <param name="vIP"></param>
        /// <returns></returns>
        public int DeleVIP(int ID)
        {
            string sql = string.Format("delete from tb_VIP where ID={0}", ID);
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 设置VIP折率
        /// </summary>
        /// <param name="discount">折率</param>
        /// <returns></returns>
        public int SetDiscount(double discount)
        {
            string sql = string.Format("update tb_Discount set VIP折率={0}",discount);
            return SQLHelper.Update(sql);
        }
    }
}
